﻿Imports System.Data.SqlClient
'Imports System.Data
'Imports Microsoft.ReportingServices.Rendering.ExcelRenderer.Excel
'Imports Microsoft.Office.Interop.Excel.XlColumnDataType
'Imports Microsoft.Office.Interop

Public Class FrmBaoCaoToanTinh
    Private conn As New SqlConnection
    Private cmd As New SqlCommand
    Private tbl As New DataSet

    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        CmbLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        CmbLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub NapDataGridViewSoTheoDoiBienDong()
        Dim TuNgay As String
        TuNgay = String.Format("{0:MM/dd/yyyy}", DateTimeTuNgay.Value)
        Dim DenNgay As String
        DenNgay = String.Format("{0:MM/dd/yyyy}", DateTimeDenNgay.Value)
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select bd.doituongid, bd.ChuSD1,  ngaydoi= convert (char (10) , bd.ngayBD, 103), bd.GioBD, bd.toBD, bd.sothua1, bd.NoiDungBD, bd.cho_o1, tx.tenxa, th.T_huyen " &
                               " From  BIENDONG bd, tenxa tx, tenhuyen th, DMTailieu tl " &
                               " WHERE bd.diachixaid = tx.maxa  " &
                               " and bd.diachihuyenid= th.mah  " &
                               " and bd.loaitailieuid= tl.maloaitailieuid " &
                               " and datediff (day, bd.ngayBD, '" & TuNgay & "') <= 0  " &
                               " and datediff (day, bd.ngayBD, '" & DenNgay & "') >= 0  " &
                               " and bd.loaitailieuid= '" & CmbLoaiTaiLieu.SelectedValue.ToString & "' Order by ngaybd  ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewBD.DataSource = tbl
        If (tbl.Rows.Count = 0) Then
            MessageBox.Show("không tìm thấy thông tin !")
        Else

        End If
        ob_cnn.Close()
    End Sub

    Private Sub FrmSoTheoDoiBienDong_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DateTimeTuNgay.Text = DateAdd(DateInterval.Year, -1, Today())
        DateTimeDenNgay.Text = Today()
        NapLoaiTaiLieu()
        'Tieude()
        DataGridViewBD.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewBD.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub

    Private Sub Buttimkiem_Click(sender As Object, e As EventArgs) Handles Buttimkiem.Click
        NapDataGridViewSoTheoDoiBienDong()

    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub
    Private Sub XuatExcel()
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "d:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        '  Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process


        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()
                .Worksheets(1).name = "So Bien Dong"
                Dim i As Integer = 1
                Dim j As Integer
                .Cells(1, 1).Value = "Họ tên người đăng ký biến động"
                .cells(1, 1).EntireRow.Font.Bold = True
                .Cells(1, 2).Value = "Ngày biến động"
                .cells(1, 2).EntireRow.Font.Bold = True
                .Cells(1, 3).Value = "Tờ bản đồ"
                .cells(1, 3).EntireRow.Font.Bold = True
                .Cells(1, 4).Value = " Số thửa"
                .cells(1, 4).EntireRow.Font.Bold = True
                .Cells(1, 5).Value = " Nội dung biến động "
                .cells(1, 5).EntireRow.Font.Bold = True
                .Cells(1, 6).Value = " Địa chỉ thửa đất "
                .cells(1, 6).EntireRow.Font.Bold = True


                Dim sql As String = "SELECT chusd1, ngaybd, tobd, sothua1, noidungbd, xa  FROM sobiendong"
                ' SqlAdapter
                Dim dscmd As New SqlDataAdapter(sql, ob_cnn)
                ' DataSet
                Dim ds As New DataSet
                dscmd.Fill(ds)

                For i = 0 To ds.Tables(0).Rows.Count - 1
                    'Column
                    For j = 0 To ds.Tables(0).Columns.Count - 1
                        ' this i change to header line cells >>>
                        .Cells(i + 3, j + 1) = _
                        ds.Tables(0).Rows(i).Item(j)

                    Next
                Next

                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu đã được xuất sang định dạng Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub


    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        LocDulieu()
        MessageBox.Show("Cập nhật thành công !", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub
    Private Sub LocDulieu()
        Dim xoa As String
        xoa = "Delete from SOBIENDONG where maBDid <> 0 "
        Dim cnn As New SqlCommand(xoa, ob_cnn)
        ob_cnn.Open()
        cnn.ExecuteNonQuery()
        ob_cnn.Close()
        Dim sMaTD As String
        Dim sChusd As String
        Dim sNgayBD As String
        Dim sGioBD As String
        Dim sToBD As String
        Dim sthua1 As String
        Dim sNoidung As String
        Dim sCho_o1 As String
        Dim sxa As String
        Dim i As Integer
        For i = 0 To DataGridViewBD.Rows.Count - 1
            sMaTD = DataGridViewBD.Rows(i).Cells(0).Value()
            sChusd = DataGridViewBD.Rows(i).Cells(1).Value()
            sNgayBD = DataGridViewBD.Rows(i).Cells(2).Value()
            sGioBD = DataGridViewBD.Rows(i).Cells(3).Value()
            sToBD = DataGridViewBD.Rows(i).Cells(4).Value()
            sthua1 = DataGridViewBD.Rows(i).Cells(5).Value()
            sNoidung = DataGridViewBD.Rows(i).Cells(6).Value()
            sCho_o1 = DataGridViewBD.Rows(i).Cells(7).Value()
            sxa = DataGridViewBD.Rows(i).Cells(8).Value()
            Dim Sql As String = ""
            Sql = "Insert Into SOBIENDONG(Doituongid, chuSD1, ngayBD, giobd, toBD, sothua1, NoidungBD, cho_o1, xa) " &
                "Values(N'" & sMaTD & "', N'" & RTrim(sMaTD) + " " + sChusd + Chr(10) + sCho_o1 & "', '" & sNgayBD.ToString + Chr(13) + Chr(10) + sGioBD & "','" & sGioBD & "', " &
                " '" & sToBD & "', '" & sthua1 & "', N'" & sNoidung & "', N'" & sCho_o1 & "', N'" & sxa & "' )"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Next
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs)

        'Dim frm As New FrmSoBD
        'frm.ShowDialog()

    End Sub


    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        XuatExcel()

    End Sub

    Private Sub DataGridViewBD_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewBD.RowPostPaint
        ' xuat so thu tu tu dong cho datagridview
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewBD.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewBD.RowHeadersWidth < Size.Width + 20 Then DataGridViewBD.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))

    End Sub

    Private Sub DataGridViewBD_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewBD.CellContentClick

    End Sub

End Class